package com.javaEE.tes.dao;

import com.javaEE.tes.dao.dto.CourseSelectionDto;
import com.javaEE.tes.entities.CourseSelection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface CourseSelectionRepository extends CrudRepository<CourseSelection,Long> {

    @Autowired
    List<CourseSelection> findAll();
    @Query(nativeQuery = true,value = "SELECT c.curriculum_name AS curriculumName,c.nature ,s.name, COUNT(sc.student_id) AS num " +
            "FROM curriculum AS c JOIN student_curriculum AS sc ON c.curriculum_id = sc.curriculum_id "+
            "LEFT JOIN student AS s ON s.student_id = sc.student_id "+
            "GROUP BY c.curriculum_name, c.nature, s.name;"
    )
    List<CourseSelectionDto> courseSelection();
}
